Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

OPEN and CLOSE QUERY statements

To get a query to retrieve data, you need to open it. When you open it, you specify the name of the query and a FOR EACH statement that references the buffers you named in the query definition, in the same order. If the query is already open, Progress closes the current open query and then reopens it. This is the general syntax:

OPEN QUERY query-name [ FOR | PRESELECT ] EACH record-phrase [ , . . .]      [ BY phrase ].

The syntax of the record-phrase is generally the same as the syntax for FOR EACH statements. If you use the PRESELECT EACH phrase instead of the FOR EACH phrase, then all the records that satisfy the query are selected and their row identifiers pre-cached, just as for a PRESELECT phrase in an ordinary data retrieval block. However, there are a few special cases for the record phrase in a query:

The first record phrase must specify EACH, and not FIRST, because the query is intended to retrieve a set of records. It is, however, valid to specify a WHERE clause in the record-phrase for the table that resulted in only a single record being selected, so a query can certainly have only one record in its result set. The record-phrase for any other buffers in the query can use the FIRST keyword instead of EACH if that is appropriate.
You cannot use the CAN-FIND keyword in a query definition. Doing so results in a compile-time error.
Queries support the use of an outer join between tables, using the OUTER-JOIN keyword, as explained below. FOR EACH statements outside of a query do not support the use of OUTER-JOIN.

Using an outer join in a query

An outer join between tables is a join that does not discard records in the first table that have no corresponding record in the second table. For example, consider this query definition:

DEFINE QUERY CustOrd FOR Customer, Order. OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer.

As Progress retrieves records to satisfy this query, it first retrieves a Customer record and then the first Order record with the same CustNum field. When you do a NEXT operation on the query, Progress locates the next Order for that Customer (if there is one), and replaces the contents of the Order buffer with the new Order. If there are no more Orders for the Customer, then Progress retrieves the next Customer and its first Order.

The question is: What happens to a Customer that has no Orders at all? The Customer does not appear in the result set for the query. The same is true for a FOR EACH block with the same record phrase. This is simply because the record phrase asks for Customers and the Orders that match them, and if there is no matching Order, then the Customer by itself does not satisfy the record phrase.

In many cases this is not the behavior you want. You want to see the Customer data regardless of whether it has any Orders or not. In this case, you can include the OUTER-JOIN keyword in the OPEN QUERY statement:

DEFINE QUERY CustOrd FOR Customer, Order. OPEN QUERY CustOrd FOR EACH Customer, EACH Order OF Customer OUTER-JOIN.

Now Progress retrieves Customers even if they have no Orders. When the Customer has no Orders, the values for all fields in the Order buffer have the Unknown value (?).

Sorting the query results

You can specify a BY phrase on your OPEN QUERY statement just as you can in a FOR EACH block. In this case, Progress either uses an index to satisfy the sort order if possible or, if no index can allow Progress to retrieve the data in the proper order, preselects and sorts all the query results before any data is made available to the application.